package cn.com.dao.impl;

import cn.com.dao.OrderDao;
import cn.com.dao.RoomDao;
import cn.com.domain.Customer;
import cn.com.domain.Room;
import cn.com.utils.JDBCutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class RoomDaoImpl implements RoomDao {
    @Override
    public List<Room> findroomByPage(int start, int rows) {
        QueryRunner runner = new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from room limit ? , ?";
        List<Room> rooms=null;
        Object[] params={start,rows};
        try {
            rooms = runner.query(sql, new BeanListHandler<Room>(Room.class),params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rooms;
    }

    /**
     * 找到room的记录数
     * @return
     */
    @Override
    public int findroomTotalCount() {
        String sql="select count(*) from room";
        Connection conn=JDBCutils.getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        int i=0;
        try {
            pstmt=conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            i = rs.getInt(1);
            JDBCutils.close(rs,pstmt,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Room> findAll() {
        QueryRunner runner = new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from room";
        List<Room> rooms=null;
        try {
            rooms = runner.query(sql, new BeanListHandler<Room>(Room.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rooms;
    }

    @Override
    public Room findRoombyroom_id(int id) {
        QueryRunner runner=new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from room where room_id = ?";
        Room room=null;
        try {
            room = runner.query(sql, new BeanHandler<>(Room.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return room;
    }

    @Override
    public void delteRoombyid(String ruid) {
        //获取room对象
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from room where room_id = ?";
        Room room=null;
        try {
            room = runner.query(sql, new BeanHandler<>(Room.class), ruid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //删除room
        sql="delete from room where room_id = ?";
        try {
            runner.update(sql,ruid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //删除对应room订单
        OrderDao orderDao=new OrderDaoImpl();
        orderDao.delteOrderbyroomName(room.getRoom_name());
    }

    @Override
    public void alterCustomerbyid(Room room, String id) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="UPDATE `room` SET `room_name`=?, " +
                "`room_price`=?, `room_area`=?, " +
                "`room_remains`=? where room_id = ?";
        Object[] params={room.getRoom_name(),room.getRoom_price(),
                room.getRoom_area(),room.getRoom_remains(),id};
        try {
            runner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Override
    public int alterRoomremainsrbyname(String name) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="UPDATE `room` SET `room_remains` =  " +
                "`room_remains`-1 where `room_name` = ?";
        int update=0;
        try {
            update = runner.update(sql, name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }
    @Override
    public int alterRoomremainsrbyname(String name,int num) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="UPDATE `room` SET `room_remains` =  " +
                "`room_remains`+ (?) where `room_name` = ?";
        int update=0;
        try {
            update = runner.update(sql,num,name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }
    @Override
    public int findRoomremainsrbyname(String name) {
        QueryRunner runner=new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from room where room_name = ?";
        int room_remains=0;
        try {
            Room room = runner.query(sql, new BeanHandler<>(Room.class), name);
            room_remains=room.getRoom_remains();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return room_remains;
    }

    @Override
    public void addRoom(Room room) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="insert into room values(?,?,?,?,?)";
        Object[] params={null,
                   room.getRoom_name(),room.getRoom_price(),
                room.getRoom_area(),room.getRoom_remains()};
        try {
            runner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
